package comframe.jdbc.sql.condition;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import comframe.jdbc.sql.support.struct.Operate;

/**
 * 查询条件
 * 
 * @author ray
 * 
 */
public class Condition {

	public Condition() {
		conditionSql = new StringBuilder();
	}

	public Condition and(Cirteria cirteria1, Cirteria cirteria2) {
		if (!"".equals(conditionSql.toString())) {
			conditionSql.append(Operate.AND);
		}
		conditionSql.append(Operate.LEFT_BRACKET).append(Operate.LEFT_BRACKET)
				.append(cirteria1.getSql()).append(Operate.RIGHT_BRACKET)
				.append(Operate.AND).append(Operate.LEFT_BRACKET).append(
						cirteria2.getSql()).append(Operate.RIGHT_BRACKET)
				.append(Operate.RIGHT_BRACKET);
		return this;
	}

	public Condition or(Cirteria cirteria1, Cirteria cirteria2) {
		if (!"".equals(conditionSql.toString())) {
			conditionSql.append(Operate.AND);
		}
		conditionSql.append(Operate.LEFT_BRACKET).append(Operate.LEFT_BRACKET)
				.append(cirteria1.getSql()).append(Operate.RIGHT_BRACKET)
				.append(Operate.OR).append(Operate.LEFT_BRACKET).append(
						cirteria2.getSql()).append(Operate.RIGHT_BRACKET)
				.append(Operate.RIGHT_BRACKET);
		return this;
	}

	public Condition createSql(Cirteria cirteria1) {
		if (!"".equals(conditionSql.toString())) {
			conditionSql.append(Operate.AND);
		}
		conditionSql.append(cirteria1.getSql());
		return this;
	}

	public Condition orderBy(String c) {
		order = Operate.ORFERBY+" " +c;
		return this;
	}

	public Cirteria createCirteria() {
		Cirteria cirteria =  new Cirteria();
		conditionSql = cirteria.getSql();
		return cirteria;
	}

	/***
	 * 内部类
	 */
	public static class Cirteria {
		private StringBuilder sql;

		public Cirteria() {
			sql = new StringBuilder();
		}

		/**
		 * and等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "=", p, value);
			} else {
				addCirteria(Operate.AND, "=", p, value);
			}
			return this;
		}
		
		public Cirteria andIn(String p, List<Object> values) {
			String value = "";
			for(Object obj:values){
				if(obj instanceof String){
					value +=",'"+obj+"'";
				}else if(obj instanceof Number){
					value +=","+obj+"";
				}
			}
			if(!"".endsWith(value))value = value.substring(1);
			if ("".equals(sql.toString())) {
				addInCirteria("", " in  ", p, "("+value+")");
			} else {
				addInCirteria(Operate.AND, " in  ", p, "("+value+")");
			}
			return this;
		}

		/**
		 * and小于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andLt(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "<", p, value);
			} else {
				addCirteria(Operate.AND, "<", p, value);
			}
			return this;
		}
		

		public Cirteria andLt(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", "<", p, value);
			} else {
				addTimeCirteria(Operate.AND, "<", p, value);
			}
			return this;
		}


		/**
		 * and小于等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andLtEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "<=", p, value);
			} else {
				addCirteria(Operate.AND, "<=", p, value);
			}
			return this;
		}
		
		public Cirteria andLtEq(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", "<=", p, value);
			} else {
				addTimeCirteria(Operate.AND, "<=", p, value);
			}
			return this;
		}


		/**
		 * and大于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andGt(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", ">", p, value);
			} else {
				addCirteria(Operate.AND, ">", p, value);
			}
			return this;
		}

		
		public Cirteria andGt(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", ">", p, value);
			} else {
				addTimeCirteria(Operate.AND, ">", p, value);
			}
			return this;
		}
		/**
		 * and大于等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andGtEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", ">=", p, value);
			} else {
				addCirteria(Operate.AND, ">=", p, value);
			}
			return this;
		}
		

		public Cirteria andGtEq(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", ">=", p, value);
			} else {
				addTimeCirteria(Operate.AND, ">=", p, value);
			}
			return this;
		}

		/**
		 * 不为空
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andIsNotNull(String p) {
			if ("".equals(sql.toString())) {
				addNullCirteria("", " is not null ", p);
			} else {
				addNullCirteria(Operate.AND, " is not null ", p);
			}
			return this;
		}

		/**
		 * 为空
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria andIsNull(String p) {
			if ("".equals(sql.toString())) {
				addNullCirteria("", " is  null ", p);
			} else {
				addNullCirteria(Operate.AND, " is  null ", p);
			}
			return this;
		}

		public Cirteria andBetween(String p, Object v1, Object v2) {
			if ("".equals(sql.toString())) {
				addCirteria("", " between ", p, v1);
				addCirteria(Operate.AND, "", "", v2);
			} else {
				addCirteria(Operate.AND, " between ", p, v1);
				addCirteria(Operate.AND, "", "", v2);
			}
			return this;
		}
		
		public Cirteria andLeftLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p, "%"+v1);
				
			} else {
				addCirteria(Operate.AND, " like ", p, "%"+v1);
				
			}
			return this;
		}
		
		public Cirteria andRightLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p, v1+"%");
				
			} else {
				addCirteria(Operate.AND, " like ", p,  v1+"%");
				
			}
			return this;
		}
		
		public Cirteria andLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p,  "%"+v1+"%");
				
			} else {
				addCirteria(Operate.AND, " like ", p,  "%"+v1+"%");
				
			}
			return this;
		}
		
		public Cirteria andNotLeftLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not like  ", p, "%"+v1);
				
			} else {
				addCirteria(Operate.AND, " not like ", p, "%"+v1);
				
			}
			return this;
		}
		
		public Cirteria andNotRightLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not  like  ", p, v1+"%");
				
			} else {
				addCirteria(Operate.AND, " not like ", p,  v1+"%");
				
			}
			return this;
		}
		
		public Cirteria andNotLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not like  ", p,  "%"+v1+"%");
				
			} else {
				addCirteria(Operate.AND, " not like ", p,  "%"+v1+"%");
				
			}
			return this;
		}

		/**
		 * or等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "=", p, value);
			} else {
				addCirteria(Operate.OR, "=", p, value);
			}
			return this;
		}
		
		public Cirteria orIn(String p, List<Object> values) {
			String value = "";
			for(Object obj:values){
				if(obj instanceof String){
					value +=",'"+obj+"'";
				}else if(obj instanceof Number){
					value +=","+obj+"";
				}
			}
			if(!"".endsWith(value))value = value.substring(1);
			if ("".equals(sql.toString())) {
				addInCirteria("", " in  ", p, "("+value+")");
			} else {
				addInCirteria(Operate.OR, " in  ", p, "("+value+")");
			}
			return this;
		}

		/**
		 * or小于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orLt(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "<", p, value);
			} else {
				addCirteria(Operate.OR, "<", p, value);
			}
			return this;
		}
		
		public Cirteria orLt(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", "<", p, value);
			} else {
				addTimeCirteria(Operate.OR, "<", p, value);
			}
			return this;
		}
		
		/**
		 *or小于等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orLtEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", "<=", p, value);
			} else {
				addCirteria(Operate.OR, "<=", p, value);
			}
			return this;
		}
		
		
		public Cirteria orLtEq(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", "<=", p, value);
			} else {
				addTimeCirteria(Operate.OR, "<=", p, value);
			}
			return this;
		}
		
		/**
		 * or大于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orGt(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", ">", p, value);
			} else {
				addCirteria(Operate.OR, ">", p, value);
			}
			return this;
		}

		public Cirteria orGt(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", ">", p, value);
			} else {
				addTimeCirteria(Operate.OR, ">", p, value);
			}
			return this;
		}
		/**
		 * or大于等于
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orGtEq(String p, Object value) {
			if ("".equals(sql.toString())) {
				addCirteria("", ">=", p, value);
			} else {
				addCirteria(Operate.OR, ">=", p, value);
			}
			return this;
		}
		

		public Cirteria orGtEq(String p, Date time, String format1,
				String format2) {
			SimpleDateFormat dateFormat = new SimpleDateFormat(format1);
			String value = "to_date('"+dateFormat.format(time)+"','"+format2+"') ";
			if ("".equals(sql.toString())) {
				addTimeCirteria("", ">=", p, value);
			} else {
				addTimeCirteria(Operate.OR, ">=", p, value);
			}
			return this;
		}

		/**
		 * 不为空
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orIsNotNull(String p) {
			if ("".equals(sql.toString())) {
				addNullCirteria("", " is not null ", p);
			} else {
				addNullCirteria(Operate.OR, " is not null ", p);
			}
			return this;
		}

		/**
		 * 为空
		 * 
		 * @param p
		 * @param value
		 * @return
		 */
		public Cirteria orIsNull(String p) {
			if ("".equals(sql.toString())) {
				addNullCirteria("", " is  null ", p);
			} else {
				addNullCirteria(Operate.OR, " is  null ", p);
			}
			return this;
		}

		public Cirteria orBetween(String p, Object v1, Object v2) {
			if ("".equals(sql.toString())) {
				addCirteria("", " between ", p, v1);
				addCirteria(Operate.AND, "", "", v2);
			} else {
				addCirteria(Operate.OR, " between ", p, v1);
				addCirteria(Operate.AND, "", "", v2);
			}
			return this;
		}

		
		public Cirteria orLeftLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p, "%"+v1);
				
			} else {
				addCirteria(Operate.OR, " like ", p, "%"+v1);
				
			}
			return this;
		}
		
		public Cirteria orRightLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p, v1+"%");
				
			} else {
				addCirteria(Operate.OR, " like ", p,  v1+"%");
				
			}
			return this;
		}
		
		public Cirteria orLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " like  ", p,  "%"+v1+"%");
				
			} else {
				addCirteria(Operate.OR, " like ", p,  "%"+v1+"%");
				
			}
			return this;
		}
		
		public Cirteria orNotLeftLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not like  ", p, "%"+v1);
				
			} else {
				addCirteria(Operate.OR, " not like ", p, "%"+v1);
				
			}
			return this;
		}
		
		public Cirteria orNotRightLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not like  ", p, v1+"%");
				
			} else {
				addCirteria(Operate.OR, " not like ", p,  v1+"%");
				
			}
			return this;
		}
		
		public Cirteria orNotLike(String p, String v1) {
			if ("".equals(sql.toString())) {
				addCirteria("", " not like  ", p,  "%"+v1+"%");
				
			} else {
				addCirteria(Operate.OR, " not like ", p,  "%"+v1+"%");
				
			}
			return this;
		}
		
		private void addCirteria(String type, String condition, String p,
				Object value) {
			if(value != null){
				if (value instanceof Number)
					sql.append(type).append(p).append(condition).append(value);
				else
					sql.append(type).append(p).append(condition).append("'")
							.append(value).append("'");
			}else
				sql.append(type).append(p).append(condition).append("'")
				.append("").append("'");
			
		}
		
		private void addInCirteria(String type, String condition, String p,
				Object value) {
			if(value != null){
					sql.append(type).append(p).append(condition).append("")
							.append(value).append("");
			}else
				sql.append(type).append(p).append(condition).append("(")
				.append("").append(")");
			
		}
		
		private void addTimeCirteria(String type, String condition, String p,
				Object value) {
			if(value != null){
					sql.append(type).append(p).append(condition).append(value);
			}else
				sql.append(type).append(p).append(condition).append("'")
				.append("").append("'");
			
		}

		private void addNullCirteria(String type, String condition, String p) {
				sql.append(type).append(p).append(condition);
		}

		
		public Cirteria andCirteria(Cirteria cirteria){
			if ("".equals(sql.toString())) {
				sql.append(Operate.LEFT_BRACKET).append(cirteria.getSql()).append(Operate.RIGHT_BRACKET);
			} else {
				sql.append(Operate.AND).append(Operate.LEFT_BRACKET).append(cirteria.getSql()).append(Operate.RIGHT_BRACKET);
			}
			return this;
		}
		
		public Cirteria orCirteria(Cirteria cirteria){
			if ("".equals(sql.toString())) {
				sql.append(Operate.LEFT_BRACKET).append(cirteria.getSql()).append(Operate.RIGHT_BRACKET);
			} else {
				sql.append(Operate.OR).append(Operate.LEFT_BRACKET).append(cirteria.getSql()).append(Operate.RIGHT_BRACKET);
			}
			return this;
		}
		
		
		public Cirteria and(Cirteria cirteria1,Cirteria cirteria2){
			if (!"".equals(sql.toString())) {
				sql.append(Operate.AND);
			}
			sql.append(Operate.LEFT_BRACKET).append(Operate.LEFT_BRACKET)
					.append(cirteria1.getSql()).append(Operate.RIGHT_BRACKET)
					.append(Operate.AND).append(Operate.LEFT_BRACKET).append(
							cirteria2.getSql()).append(Operate.RIGHT_BRACKET)
					.append(Operate.RIGHT_BRACKET);
			return this;
		}
         
		public Cirteria or(Cirteria cirteria1,Cirteria cirteria2){
			if (!"".equals(sql.toString())) {
				sql.append(Operate.AND);
			}
			sql.append(Operate.LEFT_BRACKET).append(Operate.LEFT_BRACKET)
					.append(cirteria1.getSql()).append(Operate.RIGHT_BRACKET)
					.append(Operate.OR).append(Operate.LEFT_BRACKET).append(
							cirteria2.getSql()).append(Operate.RIGHT_BRACKET)
					.append(Operate.RIGHT_BRACKET);
			return this;
		}
		
		public StringBuilder getSql() {
			return sql;
		}

		public void setSql(StringBuilder sql) {
			this.sql = sql;
		}

		

		

	}

	private StringBuilder conditionSql;
	private String order = null;

	public String getConditionSql() {
		  return conditionSql.toString();
	}

	public String getOrder() {
		return order;
	}
}
